﻿using AutoMapper;
using HongYeSystemWebApi.Templet.Entity.ApiResult;
using HongYeSystemWebApi.Templet.Entity.Entities;
using HongYeSystemWebApi.Templet.Entity.Request;
using HongYeSystemWebApi.Templet.Entity.Request.SysExpenditure;
using HongYeSystemWebApi.Templet.Entity.Request.SysProject;
using HongYeSystemWebApi.Templet.Entity.Request.SystemInvoice;
using HongYeSystemWebApi.Templet.Entity.Response;
using HongYeSystemWebApi.Templet.Entity.Response.SysExpenditure;
using HongYeSystemWebApi.Templet.Entity.Response.SysSupplier;
using HongYeSystemWebApi.Templet.Entity.Response.SystemInvoice;
using HongYeSystemWebApi.Templet.IService;
using MySqlX.XDevAPI.Relational;
using OfficeOpenXml;
using OfficeOpenXml.ConditionalFormatting;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
namespace HongYeSystemWebApi.Templet.Service
{
    public class SystemInvoiceService : ISystemInvoiceService
    {


        private readonly SqlSugarClient _db;
        private readonly IMapper _mapper;
        private readonly CommandService _comm;
        /// <summary>
        /// 构造函数依赖注入
        /// </summary>
        /// <param name="db"></param>
        public SystemInvoiceService(IHelpDB db, IMapper mapper)
        {
            this._db = db.OpenDB();
            _mapper = mapper;
            _comm = new CommandService();
        }
        public async Task DeleteAsync(MultiIdRequest request)
        {
            List<SystemInvoice> DelData = new List<SystemInvoice>();
            foreach (var item in request.Ids)
            {
                var project = await _db.Queryable<SystemInvoice>().FirstAsync(a => a.Id == item);
                project.Delete = true;
                project.OperatDate = DateTime.Now;
                DelData.Add(project);
            }
            await _db.Updateable(DelData).WhereColumns(it => new { it.Id }).ExecuteCommandAsync();
        }

        public async Task InsertAsync(SystemInvoiceInsertRequset request)
        {
            var data = _mapper.Map<SystemInvoice>(request);
            data.SystemInvoiceNumer = "HYFP" + DateTime.Now.ToString("yyyyMMddHHmmss");
            data.OperatDate = DateTime.Now;
            await _db.Insertable(data).ExecuteCommandAsync();
        }

        public async Task UpdateAsync(SystemInvoiceUpdateRequset request)
        {
            var dataFir = await _db.Queryable<SystemInvoice>().FirstAsync(X => X.Id == request.Id);
            if (dataFir == null)
            {
                throw new Exception("未找到修改原始数据");
            }
            var data = _mapper.Map<SystemInvoice>(request);
            data.OperatDate = DateTime.Now;
            await _db.Updateable(data).WhereColumns(it => new { it.Id }).ExecuteCommandAsync();
        }

        public async Task<PageModel<SystemInvoiceDataPageResponse>> GetListAsync(SystemInvoiceRequset request)
        {
            var data = _db.Queryable<SystemInvoice>().Where(x => !x.Delete);
            var asda = data.ToList();
            if (!string.IsNullOrEmpty(request.SystemInvoiceNumer))
            {
                var projectIds = await _db.Queryable<ProjectEntity>().Where(x => x.ProjectNumber == request.SystemInvoiceNumer && !x.Delete).Select(x => x.Id).ToListAsync();
                data = data.Where(x => projectIds.Contains(x.SystemInvoiceProjectId));
            }
            if (request.PartyA != 0)
            {
                data = data.Where(x => x.PartyA == request.PartyA);
            }
            if (request.SystemInvoiceProjectId != 0)
            {
                data = data.Where(x => x.SystemInvoiceProjectId == request.SystemInvoiceProjectId);
            }
            if (request.StartDate.HasValue)
            {
                data = data.Where(x => x.SystemInvoiceTime.Date >= request.StartDate.Value.Date);
            }
            if (request.EndDate.HasValue)
            {
                data = data.Where(x => x.SystemInvoiceTime.Date <= request.EndDate.Value.Date);
            }
            if (!string.IsNullOrEmpty(request.Remarks))
            {
                data = data.Where(x => x.Remarks.Contains(request.Remarks));
            }
            //开票方
            if (request.SystemCompanyId != 0)
            {
                data = data.Where(x => x.SystemCompanyId == request.SystemCompanyId);
            }
            if (request.SystemInvoiceAmount != 0)
            {
                data = data.Where(x => x.SystemInvoiceAmount == request.SystemInvoiceAmount);
            }
            if (request.SystemInvoiceIn != 0)
            {
                data = data.Where(x => x.SystemInvoiceIn == request.SystemInvoiceIn);
            }

            RefAsync<int> Total = 0;
            var resultpage = await data.OrderBy(x => x.SystemInvoiceTime, OrderByType.Desc).ToPageListAsync(request.PageNumber, request.PageSize, Total);
            var result = _mapper.Map<List<SystemInvoiceDataPageResponse>>(resultpage);
            return new PageModel<SystemInvoiceDataPageResponse>() { Data = result, Total = Total };
        }



        public async Task<ApiResult> ExportAll()
        {
            var request = new SystemInvoiceRequset();
            request.PageSize = int.MaxValue;
            request.PageNumber = 1;
            request.StartDate = null; request.EndDate = null;
            request.SystemInvoiceProjectId = 0;
            request.SystemInvoiceProjectId = 0;
            request.SystemCompanyId = 0;
            request.SystemInvoiceAmount = 0;
            request.SystemInvoiceIn = 0;
            var result = GetListAsync(request);
            return await ExportExcel(result);
        }
        public async Task<ApiResult> ExportByGetListAsync(SystemInvoiceRequset request)
        {
            var result = GetListAsync(request);
            return await ExportExcel(result);
        }
        public async Task<ApiResult> ExportExcel(Task<PageModel<SystemInvoiceDataPageResponse>> result)
        {

            List<List<string>> data = new List<List<string>>();
            var companys = await _db.Queryable<CompanyEntity>().ToListAsync();
            var projects = await _db.Queryable<ProjectEntity>().ToListAsync();
            var suppliers = await _db.Queryable<Supplier>().ToListAsync();
            var costType = await _db.Queryable<CostType>().ToListAsync();
            if (result.Result.Data.Count == 0)
            {
                throw new Exception("当前条件下没有任何数据");
            }
            string currentPath = System.IO.Directory.GetCurrentDirectory();
            var datepath = DateTime.Now.ToString("yyyyMMddHHmmss");
            var fileNamepath = Path.Combine("ExcelFile", $"{datepath}Invoice.xlsx");
            var DirPath = Path.Combine(currentPath, "ExcelFile");
            var filePath = Path.Combine(currentPath, fileNamepath);
            string currentDirectory = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "File");
            var FilesNames = Directory.GetFiles(currentDirectory);
            var TemplatefilePath = FilesNames.Where(x => x.Contains("OA发票管理需求.xlsx")).LastOrDefault();
            File.Copy(TemplatefilePath, filePath, true);
            // 加载Excel文件
            FileInfo file = new FileInfo(filePath);
            using (ExcelPackage package = new ExcelPackage(file))
            {
                ExcelPackage.LicenseContext = LicenseContext.Commercial;
                // 获取第一个工作表
                ExcelWorksheet worksheet = package.Workbook.Worksheets[0];
                var InserRowIndex = 2;
                for (int i = 0; i < result.Result.Data.Count; i++)
                {
                    var index = i + 1;
                    InserRowIndex = InserRowIndex + 1;
                    worksheet.Cells["A" + InserRowIndex].Value = index.ToString();
                    worksheet.Cells["B" + InserRowIndex].Value = result.Result.Data[i].SystemInvoiceTime.Value.Year.ToString()== "1" ? "暂无时间":result.Result.Data[i].SystemInvoiceTime?.ToString("yyyy-MM-dd");
                    worksheet.Cells["C" + InserRowIndex].Value = projects.FindLast(x => x.Id == result.Result.Data[i].SystemInvoiceProjectId)?.ProjectNumber;
                    worksheet.Cells["D" + InserRowIndex].Value = suppliers.FindLast(x => x.Id == result.Result.Data[i].PartyA)?.Name;
                    worksheet.Cells["E" + InserRowIndex].Value = companys.FindLast(x => x.Id == result.Result.Data[i].SystemCompanyId)?.CompanyName;
                    worksheet.Cells["F" + InserRowIndex].Value = result.Result.Data[i].SystemInvoiceAmount.ToString("0.00");
                    worksheet.Cells["G" + InserRowIndex].Value = result.Result.Data[i].SystemInvoiceIn.ToString("0.00");
                    worksheet.Cells["H" + InserRowIndex].Value = result.Result.Data[i].InvoiceOutTime.Value.Year.ToString() == "1" ? "暂无时间" : result.Result.Data[i].InvoiceOutTime.Value.ToString("yyyy-MM-dd");
                    worksheet.Cells["I" + InserRowIndex].Value = (result.Result.Data[i].SystemInvoiceAmount - result.Result.Data[i].SystemInvoiceIn).ToString("0.00");
                    worksheet.Cells["J" + InserRowIndex].Value = result.Result.Data[i].Remarks.ToString();
                }

                //合计模块
                worksheet.Cells["A" + (InserRowIndex+1)].Value = "合计";
                worksheet.Cells["F" + (InserRowIndex+1)].Value = result.Result.Data.Select(x=>x.SystemInvoiceAmount).Sum().ToString("0.00");
                worksheet.Cells["G" + (InserRowIndex+1)].Value = result.Result.Data.Select(x => x.SystemInvoiceIn).Sum().ToString("0.00");
                worksheet.Cells["I" + (InserRowIndex + 1)].Value = (Convert.ToDecimal(worksheet.Cells["F" + (InserRowIndex + 1)].Value)- Convert.ToDecimal(worksheet.Cells["G" + (InserRowIndex + 1)].Value)).ToString("0.00");



                var colums = _comm.GetColumnLetter(10).ToString() + (result.Result.Data.Count +2).ToString();
                // 设置边框
                worksheet.Cells[$"A1:{colums}"].Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                worksheet.Cells[$"A1:{colums}"].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                worksheet.Cells[$"A1:{colums}"].Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                worksheet.Cells[$"A1:{colums}"].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                worksheet.Cells.AutoFitColumns(); // 对整个工作表自适应
                worksheet.Cells.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; // 水平居中
                worksheet.Cells.Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center; // 垂直居中
                package.Save();
            }

            _comm.DeleteFile(DirPath);
            return ApiResult.True(fileNamepath);
        }
 
    }
}
